package com.weng.coder.common.utils;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.weng.coder.common.model.Connect;
import com.weng.coder.entity.Column;
import lombok.extern.slf4j.Slf4j;


/**
 * JDBC生成器，用于连接数据库、获取数据库表、获取数据库表元数据等操作。
 *
 * @version 
 * @author wengzhonghui  2017年7月12日 上午11:21:46
 * 
 */
@Slf4j
public class JdbcGenerator {


	/**
	 * 获取数据库连接对象
	 * 
	 * @param type
	 * @param url
	 * @param username
	 * @param password
	 * @return
	 * @throws Exception
	 */
	public static Connection getConnection(String type, String url, String username, String password) throws Exception {
		if (type.equalsIgnoreCase(DatabaseType.ORACLE.getName())) {
			Class.forName(DatabaseType.ORACLE.getClassname());
		} else if (type.equalsIgnoreCase(DatabaseType.MYSQL.getName())) {
			Class.forName(DatabaseType.MYSQL.getClassname());
//			url = "jdbc:mysql://" + url + ":3306/";
		} else if (type.equalsIgnoreCase(DatabaseType.H2.getName())) {
			Class.forName(DatabaseType.H2.getClassname());
		} else {
			throw new Exception("数据库类型有误，本系统暂不支持此数据库类型。");
		}

		// 返回数据库连接
		DriverManager.setLoginTimeout(1);// 设置连接超时
		Connection connection = DriverManager.getConnection(url, username, password);
		return connection;
	}

	/**
	 * 获取数据库连接对象
	 * 
	 * @param connect
	 * @return
	 * @throws Exception
	 */
	public static Connection getConnection(Connect connect) throws Exception {
		return getConnection(connect.getCategory(), connect.getUrl(), connect.getUsername(), connect.getPassword());
	}

	/**
	 * 获取同步表、视图信息
	 * 
	 * @param conn
	 * @return
	 * @throws SQLException
	 */
	public static Map<String, String> getTables(Connection conn,String databaseName) throws SQLException {
		DatabaseMetaData dbmd = conn.getMetaData();
		String dbType = dbmd.getDatabaseProductName();
		if (dbType.equalsIgnoreCase(DatabaseType.ORACLE.getName())) {
			return getTablesInOracle(conn);
		} else if (dbType.equalsIgnoreCase(DatabaseType.SqlServer.getName())) {
			return getTablesInSqlServer(conn);
		} else if (dbType.equalsIgnoreCase(DatabaseType.MYSQL.getName())) {
			return getTablesInMysql(conn,databaseName);
		}else if (dbType.equalsIgnoreCase(DatabaseType.H2.getName())) {
			return getTablesInH2(conn);
		}else {
			return getTablesInSimple(conn, "%");
		}
	}

	/**
	 * 获取同步表、视图信息（Oracle）
	 * 
	 * @param conn
	 * @return
	 * @throws SQLException
	 */
	private static Map<String, String> getTablesInOracle(Connection conn) throws SQLException {
		Map<String, String> map = new HashMap<>();


		StringBuffer sql = new StringBuffer("select");
		sql.append(" t.TABLE_NAME as code,");
		sql.append(" nvl(c.COMMENTS,' ') as remarks ");
		sql.append(" from USER_TABLES t,USER_TAB_COMMENTS c where t.TABLE_NAME=c.TABLE_NAME");

		try(Statement statement = conn.createStatement(); ResultSet resultSet = statement.executeQuery(sql.toString());){
			while (resultSet.next()) {
				String code = resultSet.getString("code");
				String comments = resultSet.getString("remarks");
				map.put(code, comments);
			}
		}catch (Exception e){
			log.error(e.getMessage(), e);
		}
		return map;
	}

	/**
	 * 获取同步表、视图信息（Mysql）
	 * 
	 * @param conn
	 * @return
	 * @throws SQLException
	 */
	private static Map<String, String> getTablesInMysql(Connection conn,String databaseName) throws SQLException {
		Map<String, String> map = new HashMap<String, String>();

		// 查询所有用户表
		StringBuffer sql = new StringBuffer("SELECT");
		sql.append(" t.table_name as code,");
		sql.append(" IFNULL(TABLE_COMMENT,'') as remarks ");
		sql.append(" FROM INFORMATION_SCHEMA.TABLES t");
		sql.append(" WHERE table_schema = '"+databaseName+"' ");

		try(Statement statement = conn.createStatement();ResultSet resultSet = statement.executeQuery(sql.toString());) {
			while (resultSet.next()) {
				String code = resultSet.getString("code");
				String reamrks = resultSet.getString("remarks");
				map.put(code, reamrks);
			}
		}catch (Exception e){
			log.error(e.getMessage(), e);
		}
		return map;
	}
	
	/**
	 * 获取同步表、视图信息（MS SQL Server）
	 * 
	 * @param conn
	 * @return
	 * @throws SQLException
	 */
	private static Map<String, String> getTablesInSqlServer(Connection conn) throws SQLException {
		Map<String, String> map = new HashMap<String, String>();

		// 查询所有用户表
		StringBuffer sql = new StringBuffer("SELECT");
		sql.append(" t.name as code,");
		sql.append(" isnull(e.value,'') as remarks");
		sql.append(" FROM sys.sysobjects t,sys.extended_properties e");
		sql.append(" WHERE t.id=e.major_id and e.minor_id=0 and t.xtype='U'");
		try(Statement statement = conn.createStatement();ResultSet resultSet = statement.executeQuery(sql.toString());) {
			while (resultSet.next()) {
				String code = resultSet.getString("code");
				String reamrks = resultSet.getString("remarks");
				map.put(code, reamrks);
			}
		}catch (Exception e){
			log.error(e.getMessage(), e);
		}
		return map;
	}
	
	/**
	 * 获取同步表、视图信息（H2）
	 * 
	 * @param conn
	 * @return
	 * @throws SQLException
	 */
	private static Map<String, String> getTablesInH2(Connection conn) throws SQLException {
		Map<String, String> map = new HashMap<String, String>();

		// 查询所有用户表
		StringBuffer sql = new StringBuffer("SELECT");
		sql.append(" TABLE_NAME as code,");
		sql.append(" REMARKS as remarks");
		sql.append(" FROM INFORMATION_SCHEMA.TABLES");
		sql.append(" WHERE TABLE_TYPE='TABLE'");

		try(Statement statement = conn.createStatement();ResultSet resultSet = statement.executeQuery(sql.toString());) {
			while (resultSet.next()) {
				String code = resultSet.getString("code");
				String reamrks = resultSet.getString("remarks");
				map.put(code, reamrks);
			}
		}catch (Exception e){
			log.error(e.getMessage(), e);
		}
		return map;
	}

	/**
	 * 获取同步表、视图信息（一般数据库时）
	 * 
	 * @param conn
	 * @return
	 * @throws SQLException
	 */
	private static Map<String, String> getTablesInSimple(Connection conn, String schema) throws SQLException {
		DatabaseMetaData dbmd = conn.getMetaData();
		// 获取用户信息
		ResultSet resultSet = dbmd.getTables(null, schema, "%", new String[] { "TABLE", "VIEW" });

		Map<String, String> map = new HashMap<String, String>();
		while (resultSet.next()) {
			String code = resultSet.getString("TABLE_NAME");
			String comments = resultSet.getString("REMARKS");
			log.info("TYPE_SCHEM:" + resultSet.getString("TYPE_SCHEM") + ",TABLE_TYPE:"
							+ resultSet.getString("TABLE_TYPE"));
			if (comments == null) {
				comments = "";
			}
			map.put(code, comments);
		}
		return map;
	}

	/**
	 * 获取表字段信息
	 * 
	 * @param conn
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	public static List<Column> getColumns(Connection conn, String tableName, String databaseName) throws SQLException {
		DatabaseMetaData dbmd = conn.getMetaData();
		String dbType = dbmd.getDatabaseProductName();
		if (dbType.equalsIgnoreCase(DatabaseType.ORACLE.getName())) {
			return getColumnsInOracle(conn, tableName);
		} else if (dbType.equalsIgnoreCase(DatabaseType.SqlServer.getName())) {
			return getColumnsInSqlServer(conn, tableName);
		}else if (dbType.equalsIgnoreCase(DatabaseType.MYSQL.getName())) {
			return getColumnsInMysql(conn, tableName,databaseName);
		} else if (dbType.equalsIgnoreCase(DatabaseType.H2.getName())) {
			return getColumnsInH2(conn, tableName);
		} else {
			return getColumnsInSimple(conn, tableName);
		}
	}

	/**
	 * 获取表字段信息(oracle)
	 * 
	 * @param conn
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	private static List<Column> getColumnsInOracle(Connection conn, String tableName) throws SQLException {
		StringBuffer sql = new StringBuffer("select");
		sql.append(" t.COLUMN_NAME as code,");
		sql.append(" nvl(c.COMMENTS,' ') as remarks,");
		sql.append(" nvl(t.DATA_LENGTH,0) as dataLengths,");
		sql.append(" nvl(t.DATA_PRECISION,0) as dataPrecision,");// DATA_PRECISION
		// 是数字类型的范围值
		sql.append(" nvl(t.DATA_SCALE,0) as dataScale,");
		sql.append(" nvl(t.DATA_TYPE,' ') as dataType,");
		sql.append(" t.DATA_DEFAULT as dataDefault");
		sql.append(" from USER_TAB_COLUMNS t,USER_COL_COMMENTS c");
		sql.append(" where t.TABLE_NAME=c.TABLE_NAME and t.COLUMN_NAME=c.COLUMN_NAME and t.TABLE_NAME='");
		sql.append(tableName);
		sql.append("'");

		try(Statement statement = conn.createStatement();ResultSet resultSet = statement.executeQuery(sql.toString());) {
			List<Column> columns = new ArrayList<Column>();
			while (resultSet.next()) {
				Column column = new Column();
				// 编码
				column.setCode(resultSet.getString("code"));
				// 注释
				column.setRemarks(resultSet.getString("remarks"));
				// 长度
				int lengths = resultSet.getInt("dataLengths");
				int precision = resultSet.getInt("dataPrecision");
				if (precision == 0) {
					column.setLength(lengths);
				} else {
					column.setLength(precision);
				}
				// 精度
				int scale = resultSet.getInt("dataScale");
				column.setDataPrecision(scale);
				// 数据类型
				String dataType = resultSet.getString("dataType");
				if (dataType.equals("NUMBER") && scale > 0) {
					dataType = "DOUBLE";
				} else if (dataType.indexOf("TIMESTAMP") != -1) {// TIMESTAMP(6)...
					dataType = "TIMESTAMP";
				}
				column.setDataType(dataType);

				// 默认值(数据库是Long型！)
				String dataDefault = resultSet.getString("dataDefault");
				if (dataDefault == null) {
					dataDefault = "";
				}
				column.setDefaultValue(dataDefault);
				columns.add(column);
			}
			return columns;
		}catch (Exception e){
			log.error(e.getMessage(), e);
			return null;
		}
	}

	/**
	 * 获取表字段信息(sql server)
	 * 
	 * @param conn
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	private static List<Column> getColumnsInSqlServer(Connection conn, String tableName) throws SQLException {
		StringBuffer sql = new StringBuffer("select");
		sql.append(" a.name as code,");
		sql.append(" isnull(e.[value],'') as remarks,");
		sql.append(" COLUMNPROPERTY(a.id,a.name,'PRECISION') as dataLengths,");
		sql.append(" isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as dataPrecision,");
		sql.append(" upper(b.name) as dataType,");
		sql.append(" isnull(d.text,'') as dataDefault");
		sql.append(" FROM syscolumns a");
		sql.append(" left  join systypes b on a.xusertype=b.xusertype");
		sql.append(" inner join sysobjects c on a.id=c.id and c.xtype='U' and c.name<>'dtproperties'");
		sql.append(" left  join syscomments d on a.cdefault=d.id");
		sql.append(" left  join sys.extended_properties e on a.id=e.major_id and a.colid=e.minor_id");
		sql.append(" where c.name='");
		sql.append(tableName);
		sql.append("'");

		try(Statement statement = conn.createStatement();ResultSet resultSet = statement.executeQuery(sql.toString());) {
			List<Column> columns = new ArrayList<>();
			while (resultSet.next()) {
				Column column = new Column();
				// 编码
				column.setCode(resultSet.getString("code"));
				// 注释
				column.setRemarks(resultSet.getString("remarks"));
				// 长度
				column.setLength(resultSet.getInt("dataLengths"));
				// 精度
				int scale = resultSet.getInt("dataPrecision");
				column.setDataPrecision(scale);
				// 数据类型
				column.setDataType(resultSet.getString("dataType"));
				// 默认值
				column.setDefaultValue(resultSet.getString("dataDefault"));
				columns.add(column);
			}
			return columns;
		}catch (Exception e){
			log.error(e.getMessage(), e);
			return null;
		}
	}
	
	/**
	 * 获取表字段信息(Mysql)
	 * 
	 * @param conn
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	private static List<Column> getColumnsInMysql(Connection conn, String tableName,String databaseName) throws SQLException {
		StringBuffer sql = new StringBuffer("select");
		sql.append(" c.COLUMN_NAME as code,");
		sql.append(" c.COLUMN_COMMENT as remarks,");
//		sql.append(" c.COLUMN_TYPE as dataLengths,");
//		sql.append(" c.COLUMN_TYPE as dataPrecision,");
		sql.append(" c.COLUMN_KEY as columnKey,");
		sql.append(" c.DATA_TYPE as dataType,");
		sql.append(" c.COLUMN_DEFAULT as dataDefault");
		sql.append(" FROM INFORMATION_SCHEMA.COLUMNS c");
		sql.append(" where c.TABLE_NAME='");
		sql.append(tableName);
		sql.append("' ");
		sql.append(" AND c.TABLE_SCHEMA='"+databaseName+"'  ");

		try(Statement statement = conn.createStatement();ResultSet resultSet = statement.executeQuery(sql.toString());) {
			List<Column> columns = new ArrayList<Column>();
			while (resultSet.next()) {
				Column column = new Column();
				// 编码
				column.setCode(resultSet.getString("code"));
				// 注释
				column.setRemarks(resultSet.getString("remarks"));
				// 长度
				column.setLength(255);
				// 精度
//			int scale = resultSet.getInt("dataPrecision");
				column.setDataPrecision(11);
				// 数据类型
				column.setDataType(resultSet.getString("dataType"));
				// 默认值
				String dataDefault = resultSet.getString("dataDefault");
				if(dataDefault == null || dataDefault.equals("null")){
					dataDefault = "";
				}
				//是否为主键
				String columnKey = resultSet.getString("columnKey");
				if(columnKey!=null && "PRI".equalsIgnoreCase(columnKey)){
					column.setIsPrimary(Constants.FLAG_TRUE);
				}else{
					column.setIsPrimary(Constants.FLAG_FALSE);
				}
				column.setDefaultValue(dataDefault);
				columns.add(column);
			}
			return columns;
		}catch (Exception e){
			log.error(e.getMessage(), e);
			return null;
		}
	}
	
	/**
	 * 获取表字段信息(h2)
	 * 
	 * @param conn
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	private static List<Column> getColumnsInH2(Connection conn, String tableName) throws SQLException {
		StringBuffer sql = new StringBuffer("select");
		sql.append(" c.COLUMN_NAME as code,");
		sql.append(" c.REMARKS as remarks,");
		sql.append(" c.CHARACTER_MAXIMUM_LENGTH as dataLengths,");
		sql.append(" c.NUMERIC_SCALE as dataPrecision,");
		sql.append(" t.TYPE_NAME as dataType,");
		sql.append(" c.COLUMN_DEFAULT as dataDefault");
		sql.append(" FROM INFORMATION_SCHEMA.COLUMNS c");
		sql.append(" left  join INFORMATION_SCHEMA.TYPE_INFO t on c.DATA_TYPE = t.DATA_TYPE and t.POS = 0");
		sql.append(" where c.TABLE_NAME='");
		sql.append(tableName);
		sql.append("'");

		try(Statement statement = conn.createStatement();ResultSet resultSet = statement.executeQuery(sql.toString());) {
			List<Column> columns = new ArrayList<Column>();
			while (resultSet.next()) {
				Column column = new Column();
				// 编码
				column.setCode(resultSet.getString("code"));
				// 注释
				column.setRemarks(resultSet.getString("remarks"));
				// 长度
				column.setLength(resultSet.getInt("dataLengths"));
				// 精度
				int scale = resultSet.getInt("dataPrecision");
				column.setDataPrecision(scale);
				// 数据类型
				column.setDataType(resultSet.getString("dataType"));
				// 默认值
				String dataDefault = resultSet.getString("dataDefault");
				if (dataDefault == null || dataDefault.equals("null")) {
					dataDefault = "";
				}
				column.setDefaultValue(dataDefault);
				columns.add(column);
			}
			return columns;
		}catch (Exception e){
			log.error(e.getMessage(), e);
		}
		return  null;
	}

	/**
	 * 获取表字段信息(一般数据库)
	 * 
	 * @param conn
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	private static List<Column> getColumnsInSimple(Connection conn, String tableName) throws SQLException {
		DatabaseMetaData dbmd = conn.getMetaData();
		// 获取用户信息
		String userName = dbmd.getUserName();
		ResultSet resultSet = dbmd.getColumns(null, userName, tableName, null);

		List<Column> columns = new ArrayList<Column>();
		while (resultSet.next()) {
			Column column = new Column();
			column.setCode(resultSet.getString("COLUMN_NAME"));
			column.setRemarks(resultSet.getString("REMARKS"));
			column.setDataType(resultSet.getString("TYPE_NAME"));
			column.setLength(resultSet.getInt("COLUMN_SIZE"));
			column.setDataPrecision(resultSet.getInt("NUM_PREC_RADIX"));
			column.setDefaultValue(resultSet.getString("COLUMN_DEF"));
			columns.add(column);
		}

		return columns;
	}

	/**
	 * 根据字段编码获取字段名字
	 * 
	 * @param code
	 * @return
	 */
	public static String getFieldName(String code) {
		if (code.indexOf("_") == -1) {
			return code.toLowerCase();
		}
		String[] strings = code.split("_");
		StringBuffer sb = new StringBuffer();
		sb.append(strings[0].toLowerCase());
		for (int i = 1; i < strings.length; i++) {
			sb.append(strings[i].substring(0, 1).toUpperCase());
			sb.append(strings[i].substring(1).toLowerCase());
		}
		return sb.toString();
	}

}
